package com.example.demo.Services.impl;

import com.example.demo.Services.TjServices;
import com.example.demo.entiy.*;
import com.example.demo.utils.MyUtils;
import com.example.demo.utils.SqlHelper;
import com.sun.mail.imap.protocol.ID;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

@Component
public class TjServicesImpl implements TjServices {
    Connection conn;
    Statement st;
    ResultSet rs;
    PreparedStatement ps;
    SqlHelper sqlHelper=new SqlHelper();
    MyUtils myUtils=new MyUtils();
    private final Logger log=Logger.getLogger(this.getClass());
    /**
     * 根据empidx搜索详情
     * @param empidx
     * @return
     */
    @Override
    public List<Bgd> selectAll(int empidx) {
        List<Bgd> li= new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql="select a.idx as aidx,a.*,b.* from td_tj_bhk(nolock) a,td_tj_dgn(nolock) b where a.idx=b.bhkidx and" +
                " a.empidx="+empidx+" order by a.bhkdat desc";
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                Bgd bgd=new Bgd();
                bgd.setIdx(rs.getString("idx"));
                bgd.setEmpidc(rs.getString("empidx"));
                bgd.setTjbm(rs.getString("bhkcod"));
                bgd.setAge(rs.getInt("age"));
                bgd.setBirth( rs.getTimestamp("brth").toString().substring(0,10));
                bgd.setIdc(rs.getString("idc"));
                bgd.setName(rs.getString("empnam"));
                bgd.setTjjg(rs.getString("bhkrsl"));
                bgd.setSex(rs.getString("sex"));
                bgd.setJy(rs.getString("dgnadv"));
                bgd.setBhkdat(rs.getTimestamp("bhkdat").toString().substring(0,10));
                li.add(bgd);
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(empidx+"---------"+e.getMessage());
        }

        return li;
    }

    /**
     * 根据身份证号搜索empidx
     * @param IDC
     * @return
     */
    @Override
    public int selectEmpidx(String IDC,String psw,int flag) {
        int empidx=0;
        conn=sqlHelper.getConnection();
        String sql="";
        if (flag==1) {
            sql = "select * from td_tj_bhk(nolock) where  idc='" + IDC + "'";
        }else {
            sql = "select * from td_tj_bhk(nolock) where  empnam='" + IDC + "' and  psw='"+psw+"'";
        }
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
                while (rs.next()) {
                    empidx = rs.getInt("empidx");
                }


            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(e.getMessage());
        }
        return empidx;
    }

    @Override
    public Bgd selectOne(int tjbm) {
        Bgd bgd=new Bgd();
        bgd.setTjbm(tjbm+"");
        conn=sqlHelper.getConnection();
        String sql ="select a.*,b.* from td_tj_bhk(nolock) a ,td_tj_dgn(nolock) b where a.idx=b.bhkidx and a.idx="+tjbm;
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while(rs.next()){
                bgd.setIdx(rs.getString("idx"));
                bgd.setCrptnam(rs.getString("crptnam"));
                bgd.setBhkcod(rs.getString("bhkcod"));
                bgd.setName(rs.getString("empnam"));
                bgd.setBhkdat(rs.getTimestamp("bhkdat").toString().substring(0,10));
                bgd.setSex(rs.getString("sex"));
                bgd.setAge(rs.getInt("age"));
                bgd.setTjjg(rs.getString("bhkrsl"));
                bgd.setMhkadv(rs.getString("mhkadv"));
                bgd.setJy(rs.getString("dgnadv"));
                bgd.setBhksrtidx(rs.getString("bhksrtidx"));
                bgd.setIdc(rs.getString("idc"));
                bgd.setWrknum(rs.getString("wrknum"));
                bgd.setDpt(rs.getString("dpt"));
                bgd.setWRKLNT(rs.getString("WRKLNT"));
                bgd.setWRKLNTMONTH(rs.getString("WRKLNTMONTH"));
                bgd.setTCHBADRSNTIM(rs.getString("TCHBADRSNTIM"));
                bgd.setTCHBADRSNMONTH(rs.getString("TCHBADRSNMONTH"));
                bgd.setTCHBADRSN(rs.getString("TCHBADRSN"));
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(tjbm+"---------"+e.getMessage());
        }
        bgd.setWrkstanam(selectWrkstanam(tjbm));
        return bgd;
    }

    @Override
    public String selectWrkstanam(int tjbm) {
        conn=sqlHelper.getConnection();
        String sql="select b.wrksta as wrkstanam from td_tj_bhk(nolock) a, tb_tj_wrksta(nolock) b where a.WRKSTAIDX=b.idx and a.idx="+tjbm;
        String nn="";
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                if (rs.getString("wrkstanam").equals("")){
                    nn="--";
                }else {
                    nn=rs.getString("wrkstanam");
                }
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(tjbm+"---------"+e.getMessage());
        }

        return nn;
    }

    @Override
    public List<XM> selectXm(int tjbm) {
        List<XM> li =new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql="select isnull(e.bsnssrt,'') as bsnssrt,b.idx,c.itmnam,c.jdgptn,c.idx as itmidx,b.rslt,b.rgltag,c.dflt,f.bhkrsl,f.mhkadv,f.dgnadv,g.stnd,g.msrunt from" +
                " (((((td_tj_bhk(nolock) a left join td_tj_dgn(nolock) f on a.idx=f.bhkidx) left join td_tj_bhksub_old(nolock) b on a.idx=b.mtbidx)" +
                "  left join tb_tj_bhkitm(nolock) c on b.bhkitmidx=c.idx) left join tb_tj_bsnssrtitmrlv(nolock) d on c.idx=d.bhkitmidx) left join" +
                "   tb_tj_bsnssrt(nolock) e on d.bsnssrtidx=e.idx) left join tb_tj_bhkitm(nolock) g on g.idx=c.idx  where a.idx="+tjbm+" order by e.rptsrtidx,e.num,e.idx";
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                XM xm=new XM();
                if (!rs.getString("bsnssrt").equals("")) {
                    if (myUtils.isExits(rs.getString("bsnssrt"), li)) {
                        xm.setXmlb(rs.getString("bsnssrt"));
                    }
                }
                
                if (rs.getString("itmnam").equals("血清HP"))
                    System.out.println(rs.getString("rslt"));
                xm.setXmNam(rs.getString("itmnam"));
                xm.setRes(rs.getString("rslt"));
                if (!rs.getString("rslt").equals(rs.getString("stnd"))) {
                    String sss=rs.getString("stnd");
                    if (sss!=null){
                        if (sss.contains("-")){
                            xm.setMax(Double.parseDouble(sss.substring(sss.indexOf("-")+1,sss.length())));
                            xm.setMin(Double.parseDouble(sss.substring(0,sss.indexOf("-"))));
                        }
                    }
                    if (rs.getString("msrunt") == null) {
                        xm.setCgz(rs.getString("stnd"));
                    } else {

                        xm.setCgz(rs.getString("stnd") + "&nbsp;" + rs.getString("msrunt"));
                    }
                }
                li.add(xm);
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(tjbm+"-----------"+e.getMessage());
        }


        return li;
    }

    @Override
    public String selectPsw(String idc,String psd,int flag) {
        conn= sqlHelper.getConnection();
        String psw="";
        String sql="";
        if (flag==1) {
            sql = "select * from td_tj_bhk(nolock) where idc='" + idc + "'";
        }else {
            sql = "select * from td_tj_bhk(nolock) where empnam='" + idc + "' and  psw='"+psd+"'";
        }
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                psw=rs.getString("psw");
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(e.getMessage());
        }

        return psw;
    }

    @Override
    public List<EmploymentHistory> selEHByIdx(String idx) {
        List<EmploymentHistory> li=new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql= "select * from TD_TJ_PRFHST(nolock) where BHKMTBIDX="+idx;
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                EmploymentHistory e=new EmploymentHistory();
                e.setBADRSN(rs.getString("BADRSN"));
                e.setDEPTSHP(rs.getString("DEPTSHP"));
                e.setSFQ(rs.getString("SFQ"));
                e.setSTRSTPDAT(rs.getString("STRSTPDAT"));
                e.setWRK(rs.getString("WRK"));
                e.setWRKUNT(rs.getString("WRKUNT"));
                li.add(e);
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(idx+"----------------"+e.getMessage());
        }

        return li;

    }

    @Override
    public List<PastMedicalHistory> selPMHByIDX(String idx) {
        List<PastMedicalHistory> li=new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql="select * from TD_TJ_EXMHST(nolock) where BHKMTBIDX="+idx;
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()) {
                PastMedicalHistory p=new PastMedicalHistory();

                p.setHSTCRUPRC(rs.getString("HSTCRUPRC"));

                p.setHSTDAT(rs.getString("HSTDAT"));

                p.setHSTLPS(rs.getString("HSTLPS"));
                if (!rs.getString("HSTNAM").equals("无")) {
                    p.setHSTNAM(rs.getString("HSTNAM"));
                }else {
                    p.setHSTNAM(null);
                }
                p.setHSTUNT(rs.getString("HSTUNT"));

                    li.add(p);

            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(e.getMessage());
        }catch (NullPointerException n){
            li=null;
        }
        return li;
    }

    @Override
    public List<BirthHistory> selBhByIDX(String idx) {
        List<BirthHistory> li=new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql="select * from TB_TJ_EXMITM(nolock) where BHKMTBIDX="+idx;
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                BirthHistory b=new BirthHistory();
                b.setABRQTY(rs.getString("ABRQTY"));
                b.setCHLDQTY(rs.getString("CHLDQTY"));
                b.setSLNKQTY(rs.getString("SLNKQTY"));
                b.setSTLQTY(rs.getString("STLQTY"));
                b.setTRSQTY(rs.getString("TRSQTY"));
                if (b.getABRQTY().equals(null)&&b.getCHLDQTY().equals(null)&&b.getSLNKQTY().equals(null)&&b.getSTLQTY().equals(null)&&b.getTRSQTY().equals(null)) {
                }else {
                    li.add(b);
                }
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(idx+"----------------"+e.getMessage());
        }catch (NullPointerException n){
            li=null;
        }

        return li;
    }

    @Override
    public List<MenstrualHistory> selMHByIdx(String idx) {
        List<MenstrualHistory> li=new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql="select * from TB_TJ_EXMITM(nolock) where BHKMTBIDX="+idx;
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                MenstrualHistory b=new MenstrualHistory();
                b.setCYC(rs.getString("CYC"));
                b.setISXMNS(rs.getString("ISXMNS"));
                b.setMNLAGE(rs.getString("MNLAGE"));
                b.setMNRAGE(rs.getString("MNRAGE"));
                b.setMNS(rs.getString("MNS"));
                if (b.getCYC().equals(null)&&b.getISXMNS().equals(null)&&b.getMNLAGE().equals(null)&&b.getMNRAGE().equals(null)&&b.getMNS().equals(null)){

                }else {
                    li.add(b);
                }
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(idx+"----------------"+e.getMessage());
        }catch (NullPointerException n){
            li=null;
        }

        return li;
    }

    @Override
    public List<zonghe> selZhByIDX(String idx) {
        List<zonghe> li=new LinkedList<>();
        conn=sqlHelper.getConnection();
        String sql="select * from TB_TJ_EXMITM(nolock) where BHKMTBIDX="+idx;
        try {
            st=conn.createStatement();
            rs=st.executeQuery(sql);
            while (rs.next()){
                zonghe z=new zonghe();
                z.setSMKDAYBLE(rs.getString("SMKDAYBLE"));
                z.setSMKSTA(rs.getString("SMKSTA"));
                z.setSMKYERQTY(rs.getString("SMKYERQTY"));
                z.setWINDAYMLX(rs.getString("WINDAYMLX"));
                z.setWINSTA(rs.getString("WINSTA"));
                z.setWINYERQTY(rs.getString("WINYERQTY"));
                z.setOTH(rs.getString("OTH"));
                li.add(z);
            }
            sqlHelper.colse(conn,st,ps,rs);
        } catch (SQLException e) {
            log.error(idx+"----------------"+e.getMessage());
        }catch (NullPointerException n){
            li=null;
        }
        return li;
    }


}
